import json
import psycopg2




class DBManager(object):

    def __init__(self,HOST,db_name ='cgai01',auto_create=True):
        super(DBManager,self).__init__()
        self.HOST = HOST
        self.USER = 'mz'
        self.PASSWORD = '123456'
        self.PORT = 5432
        self.db_name = self.use_db(db_name,auto_create) #当前操作的数据库
        # self.PASSWORD = PASSWORD
        
        # self.PORT = PORT
        # self.ct = TimeHandler()


    def default_connect(self):
        """
        默认连接postgres数据库
        :return: connect, cursor
        """
        try:
            connect = psycopg2.connect(database='postgres',
                                          user=self.USER,
                                          password=self.PASSWORD,
                                          host=self.HOST,
                                          port=self.PORT)
            cursor = connect.cursor()
        except Exception as _default_connect_ERR:
            print('_default_connect_ERR:', _default_connect_ERR)
            return None, None
        return connect, cursor




    def init_db(self):
        """
        初始化231 cgtw数据库连接
        :param db_name: 默认连接自带的postgres数据库
        :return: connect, cursor
        """
        try:
            connect = psycopg2.connect(database=self.db_name,
                                          user=self.USER,
                                          password=self.PASSWORD,
                                          host=self.HOST,
                                          port=self.PORT)
            cursor = connect.cursor()
        except Exception as init_db_ERR:
            print('init_db_ERR:', init_db_ERR)
            return None, None
        return connect, cursor


    """
    -------------------------------------------------------------------------------
    数据库操作
    -------------------------------------------------------------------------------
    """
    def use_db(self,db_name,auto_create):
        """
        切换指定数据库
        :param db_name:
        :param auto_create:
        :return:
        """
        db = ''
        select_sql = "select * from pg_database where datname='{}'".format(db_name)
        sql = "create database {} ".format(db_name)
        connect, cursor = self.default_connect()
        try:
            cursor.execute(select_sql)
            connect.commit()
            result = cursor.fetchone()
            if not result and auto_create:
                connect.autocommit = True
                cursor.execute(sql)
                connect.autocommit = False
                db = db_name
                print('创建成功')
            else:
                print('数据库{}已经存在,执行该数据库操作'.format(db_name))
                db = db_name #result[0]
        except Exception as get_department_info_ERR:
            print('创建失败')
            print('get_department_info_ERR:', str(get_department_info_ERR))

        finally:
            connect.close()
        print('db_name:',db)
        return db

    def create_db(self,db_name):
        """
        创建新的数据库

        :return:
        """
        select_sql ="select * from pg_database where datname='{}'".format(db_name)
        sql ="create database {} ".format(db_name)
        connect, cursor = self.default_connect()
        try:
            cursor.execute(select_sql)
            connect.commit()
            result = cursor.fetchall()
            if not result:
                connect.autocommit =True
                cursor.execute(sql)
                connect.autocommit =False
                print('创建成功')
            else:
                print('数据库{}已经存在'.format(db_name))

        except Exception as get_department_info_ERR:
            print('创建失败')
            print('get_department_info_ERR:', str(get_department_info_ERR))

        finally:
            connect.close()      




    """
    -------------------------------------------------------------------------------
    schema操作
    -------------------------------------------------------------------------------
    """



    def select_schema(self):
        sql ="SELECT nspname FROM pg_namespace"

        connect, cursor = self.init_db()
        try:
            cursor.execute(sql)
            result =cursor.fetchall()
            if result:
                for i in result:
                    print(i)
            else:
                print('没有schema')
        except Exception as select_schema_ERR:
            print(str(select_schema_ERR))


    def create_schema(self,schema_name):
        """
        创建schema
        :param db_name:
        :param schema_name:
        :return:
        """
        success = False
        sql ="create schema {}".format(schema_name)
        connect, cursor = self.init_db()
        try:
            cursor.execute(sql)
            connect.commit()
            success = True
            print('schema创建成功')
        except Exception as creat_schema_ERR:
            print('creat_schema_ERR:',str(creat_schema_ERR))
        finally:
            connect.close()
        return success


    def get_accounts(self):
        """
        获取人员账户信息

        返回： {id:data,...}
        """
        account_data = {}

        sql = "select id,username,password,active,index,level,department,wish_money,ip,config,info from cgai.account "

        connect, cursor = self.init_db()
        try:
            cursor.execute(sql)
            results = cursor.fetchall()
            if results:
                for i in results:
                    data = {}
                    data['id'],data['username'],data['password'],data['active'],data['index'],\
                    data['level'],data['department'],data['wish_money'],data['ip'],data['config'],\
                    data['info'] = i 

                    account_data[data['username']] = data
        except Exception as get_accounts_ERR:
            print('get_accounts_ERR:',str(get_accounts_ERR))
        finally:
            connect.close()
        return account_data


        